/**
 * UserDatabase.java
 * Provides access to the User database.
 * 
 * @author Ryan Senkbeil
 */

package com.msoe.core.database;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.msoe.core.users.AccountType;
import com.msoe.core.users.Password;
import com.msoe.core.users.User;

public class UserDatabase 
{
	private static UserDatabase instance;
	private Connection connection;
	
	private UserDatabase()
	{
		// Get a connection to the database.
		this.connection = Database.getInstance().getConenction();
	}
	
	/**
	 * Returns the instance of the UserDatabase class so it can be worked with.
	 * @return An instance of the UserDatabase class.
	 */
	public static UserDatabase getInstance()
	{
		if(instance == null) instance = new UserDatabase();
		
		return instance;
	}
	
	/**
	 * Add a new user to the database if the username or email is not already taken.
	 * @param toAdd - The user to add
	 * @return True if the user was successfully added, otherwise false.
	 */
	public boolean addUser(User toAdd)
	{
		boolean retValue = false;
		
		String sql = "INSERT into USERS values (?,?,?,?,?,?,?,?,?,?) ";
		
		try 
		{
			PreparedStatement prep = connection.prepareStatement(sql);
			
			prep.setInt(1, getNextUserID());
			prep.setString(2, toAdd.getUsername());
			prep.setString(3, toAdd.getEmail());
			prep.setString(4, toAdd.getFirstName());
			prep.setString(5, toAdd.getLastName());
			prep.setString(6, toAdd.getPassword().getSalt());
			prep.setString(7, toAdd.getPassword().getHash());
			prep.setInt(8, toAdd.getAccountType().ordinal());
			prep.setBoolean(9, false);
			prep.setBoolean(10, false);
			
			prep.executeUpdate();
			retValue = true;
			
			this.connection.commit();
		} 
		catch (SQLException e)
		{
			throw new DatabaseException(e);
		}
		
		return retValue;
	}
	
	private int getNextUserID()
	{
		int retValue = -1;
		
		String sql = "SELECT max(uid) from users;";
		
		try
		{
			PreparedStatement prep = connection.prepareStatement(sql);
			
			ResultSet results = prep.executeQuery();
			
			if(results.next()) retValue = results.getInt(1);
		}
		catch(SQLException e)
		{
			throw new DatabaseException(e);
		}
		
		return retValue + 1;
	}

	/**
	 * Changes the users current password.
	 * @param toChange - The user who wants their password changed.
	 * @param newPassword - The new password.
	 * @return True if the password was successfully changed, otherwise false.
	 */
	public boolean changePassword(User toChange, Password newPassword)
	{
		boolean retValue = false;
		
		String sql = "UPDATE USERS SET salt=?, password=? WHERE uid=?";
		
		try 
		{
			PreparedStatement prep = connection.prepareStatement(sql);
			
			prep.setString(1, newPassword.getSalt());
			prep.setString(2, newPassword.getHash());
			prep.setInt(3, toChange.getUserID());
			
			prep.executeUpdate();
			
			this.connection.commit();
			
			retValue = true;
		} 
		catch (SQLException e)
		{
			throw new DatabaseException(e);
		}
		
		return retValue;
	}
	
	/**
	 * Removes a user from the database.
	 * @param toRemove - The user to remove.
	 * @return True if the user was successfully removed, otherwise false.
	 */
	public boolean removeUser(User toRemove)
	{
		boolean retValue = false;
		
		String sql = "DELETE FROM users WHERE uid=?";
		
		try 
		{
			PreparedStatement prep = connection.prepareStatement(sql);
			
			prep.setInt(1, toRemove.getUserID());
			
			prep.executeUpdate();
			
			this.connection.commit();
			
			retValue = true;
		} 
		catch (SQLException e)
		{
			throw new DatabaseException(e);
		}
		
		return retValue;
	}
	
	/**
	 * Gets a user by username.
	 * @param username - The username of the user to get.
	 * @return A User containing all the information from the database.
	 */
	public User getUser(String username)
	{
		User toRet = new User();
		
		String sql = "SELECT * FROM users WHERE username=?";
		
		try 
		{
			PreparedStatement prep = connection.prepareStatement(sql);
			
			prep.setString(1, username);
			
			ResultSet results = prep.executeQuery();
			
			int count = 0;
			while(results.next())
			{
				if(count > 1) throw new DatabaseException("One user was expected, but more were retrieved from the database.");
				
				toRet.setAccountType(results.getInt("acct_type") == 0 ? AccountType.Administrator : AccountType.Standard);
				toRet.setEmail(results.getString("email"));
				toRet.setFirstName(results.getString("fname"));
				toRet.setLastName(results.getString("lname"));
				toRet.setPassword(new Password(results.getString("salt"), results.getString("password")));
				toRet.setUserID(results.getInt("uid"));
				toRet.setUsername(results.getString("username"));
				
				count++;
			}
		} 
		catch (SQLException e)
		{
			throw new DatabaseException(e);
		}
		
		return toRet;
	}
	
	/**
	 * Gets a user by user id.
	 * @param userID - The user id of the user to get.
	 * @return A User containing all the information from the database.
	 */
	public User getUser(int userID)
	{
		User toRet = new User();
		
		String sql = "SELECT * FROM users WHERE uid=?";
		
		try 
		{
			PreparedStatement prep = connection.prepareStatement(sql);
			
			prep.setInt(1, userID);
			
			ResultSet results = prep.executeQuery();
			
			int count = 0;
			while(results.next())
			{
				if(count > 1) throw new DatabaseException("One user was expected, but more were retrieved from the database.");
				
				toRet.setAccountType(results.getInt("acct_type") == 0 ? AccountType.Administrator : AccountType.Standard);
				toRet.setEmail(results.getString("email"));
				toRet.setFirstName(results.getString("fname"));
				toRet.setLastName(results.getString("lname"));
				toRet.setPassword(new Password(results.getString("salt"), results.getString("password")));
				toRet.setUserID(results.getInt("uid"));
				toRet.setUsername(results.getString("username"));
				
				count++;
			}
		} 
		catch (SQLException e)
		{
			throw new DatabaseException(e);
		}
		
		return toRet;
	}
	
	/**
	 * Bans a user from accessing the application.
	 * @param toBan - The user that should be banned.
	 * @return True if the user was successfully banned, otherwise false.
	 */
	public boolean banUser(User toBan)
	{
		return this.setBan(toBan, true);
	}
	
	/**
	 * Allows a banned user to access the application again.
	 * @param toUnban - The user to un-banned
	 * @return True if the user was successfully un-banned, otherwise false.
	 */
	public boolean unbanUser(User toUnban)
	{
		return this.setBan(toUnban, false);
	}
	
	/**
	 * Sets the current ban status of the user.
	 * @param toSet - The user to set the ban status
	 * @param ban - True if the user should be banned, otherwise false.
	 * @return
	 */
	private boolean setBan(User toSet, boolean ban)
	{
		boolean retValue = false;
		
		String sql = "UPDATE USERS SET locked_out=? WHERE uid=?";
		
		try 
		{
			PreparedStatement prep = connection.prepareStatement(sql);
			
			prep.setBoolean(1, ban);
			prep.setInt(2, toSet.getUserID());
			
			prep.executeUpdate();

			this.connection.commit();
			
			retValue = true;
		} 
		catch (SQLException e)
		{
			throw new DatabaseException(e);
		}
		
		return retValue;
	}
	
	public boolean getBannned(int userid)
	{
		String sql = "SELECT locked_out FROM USERS WHERE uid=?";
		
		try 
		{
			PreparedStatement prep = connection.prepareStatement(sql);
			
			prep.setInt(1, userid);
			
			ResultSet result = prep.executeQuery();
			result.next();
			
			return result.getBoolean("locked_out");
		} 
		catch (SQLException e)
		{
			throw new DatabaseException(e);
		}
	}
	
	public boolean setEmailVerified(int userID, boolean verified)
	{
		boolean retValue = false;
		
		String sql = "UPDATE USERS SET email_verified=? WHERE uid=?";
		
		try 
		{
			PreparedStatement prep = connection.prepareStatement(sql);
			
			prep.setBoolean(1, verified);
			prep.setInt(2, userID);
			
			prep.executeUpdate();

			this.connection.commit();
			
			retValue = true;
		} 
		catch (SQLException e)
		{
			throw new DatabaseException(e);
		}
		
		return retValue;
	}
}
